﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinExamReader
{
    public static class ExcelHelper
    {
        /// <summary>
        /// 天啊，万能的读取Excel啊
        /// </summary>
        /// <param name="path"></param>
        /// <remarks>yumi</remarks>
        /// <returns></returns>
        private static DataTable ImportFromXls(string path)
        {
            IWorkbook workbook = null;
            if (path.ToLower().EndsWith(".xls"))
            {
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    workbook = new HSSFWorkbook(file);
                }
            }
            else
            {
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    workbook = new XSSFWorkbook(file);
                }
            }
            ISheet sheet = workbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            for (int j = 0; j < 1; j++)
            {
                dt.Columns.Add("C1");
            }
            while (rows.MoveNext())
            {
                IRow row = (IRow)rows.Current;
                int cCount = row.LastCellNum > row.Cells.Count ? row.LastCellNum : row.Cells.Count;
                int cy = cCount - dt.Columns.Count;
                int nowCount = dt.Columns.Count;
                if (cy > 0)
                {
                    for (int j = nowCount + 1; j < cCount + 1; j++)
                    {
                        dt.Columns.Add("C" + j);
                    }
                }
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 从给定的Excel文件获取Datatable对象。默认从Excel的Sheet1中读取。
        /// </summary>
        /// <param name="fileName">Excel文件路径</param>
        /// <param name="convertColumn">是否从第一行提取为列头</param>
        /// <returns></returns>
        /// <exception cref="FileNotFoundException">给定的Excel文件不存在。</exception>
        /// <exception cref="ArgumentException">给定的文件不是Excel格式。</exception>
        public static DataTable GetDataTableFromExcel(string fileName, bool convertColumn)
        {
            DataTable table = GetDataTableFromExcel(fileName);
            if (convertColumn && table != null && table.Rows.Count > 0)
            {
                int i = 1;
                foreach (DataColumn dc in table.Columns)
                {
                    string dcName = table.Rows[0][dc.ColumnName].ToString();
                    dc.ColumnName = string.IsNullOrWhiteSpace(dcName) ? ("Default" + (i++).ToString()) : dcName;
                }
                try
                {
                    table.Rows.RemoveAt(0);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            return table;
        }


        /// <summary>
        /// 从给定的Excel文件获取Datatable对象。默认从Excel的Sheet1中读取。
        /// </summary>
        /// <param name="fileName">Excel文件路径</param>
        /// <exception cref="FileNotFoundException">给定的Excel文件不存在。</exception>
        /// <exception cref="ArgumentException">给定的文件不是Excel格式。</exception>
        /// <returns></returns>
        public static DataTable GetDataTableFromExcel(string fileName)
        {
            if (!File.Exists(fileName))
            {
                throw new FileNotFoundException("给定的Excel文件不存在。");
            }
            string ext = Path.GetExtension(fileName).Trim('.').ToLower();
            if (!".xls.xlsx".Contains(ext))
            {
                throw new ArgumentException("给定的文件不是Excel格式。");
            }
            DataTable dt = new DataTable();
            try
            {
                dt = ImportFromXls(fileName);
            }
            catch (Exception ex)
            {
                throw new Exception("读取文件出错，请确保不是拿腾讯文档创建的该文件："+ex.Message);
            }
            return dt;
        }

        public static void SaveTable(DataGridView dgv, string path)
        {
            //创建工作薄
            HSSFWorkbook wk = new HSSFWorkbook();

            CreateDetail(dgv, wk);//得分明细
            CreateSummary((DataTable)dgv.DataSource, wk);
            using (FileStream fs = File.OpenWrite(path)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                wk.Write(fs);   
                Console.Write("提示：创建成功！");
            }
        }

        private static void CreateSummary(DataTable dtYuan, HSSFWorkbook wk)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("姓名");
            dt.Columns.Add("成绩", typeof(int));

            for (int i = 2; i < dtYuan.Columns.Count; i++)
            {
                DataRow dr = dt.NewRow();
                dr[0] = dtYuan.Columns[i].ColumnName;
                dr[1] = dtYuan.Rows[dtYuan.Rows.Count - 1][dtYuan.Columns[i].ColumnName].ToString();
                dt.Rows.Add(dr);
            }

            DataView dv = new DataView(dt);
            dv.Sort = "成绩 desc";

            DataTable dtNew=dv.ToTable();
            ISheet tb = wk.CreateSheet("成绩排名");
            IRow rowFirst = tb.CreateRow(0);
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                ICell cell = rowFirst.CreateCell(i);
                cell.SetCellValue(dtNew.Columns[i].ColumnName);
            }

            for (int i = 0; i < dtNew.Rows.Count; i++)
            {
                IRow row = tb.CreateRow(i + 1);
                for (int j = 0; j < dtNew.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(dtNew.Rows[i][j].ToString());
                    
                }
            }
        }

        private static void CreateDetail(DataGridView dgv, HSSFWorkbook wk)
        {
            ICellStyle s = getWrongStyle(wk);

            ISheet tb = wk.CreateSheet("得分明细");
            IRow rowFirst = tb.CreateRow(0);
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                ICell cell = rowFirst.CreateCell(i);
                cell.SetCellValue(dgv.Columns[i].Name);
            }

            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                IRow row = tb.CreateRow(i + 1);
                for (int j = 0; j < dgv.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
                    if (dgv.Rows[i].Cells[j].Style.BackColor == Color.Red)
                    {
                        cell.CellStyle = s;
                    }
                }
            }
        }

        public static ICellStyle getWrongStyle(HSSFWorkbook wk)
        {
            ICellStyle s = wk.CreateCellStyle();
            s.FillForegroundColor = HSSFColor.Red.Index;
            s.FillPattern = FillPattern.SolidForeground;

            HSSFFont ffont = (HSSFFont)wk.CreateFont();
            //给字体设置颜色
            ffont.Color = HSSFColor.White.Index;
            s.SetFont(ffont);
            return s;
        }
    }
}
